pandas 读取和保存到mysql

安装

pip install sqlalchemy

读取

import pandas as pd
import tushare as ts
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@localhost:3306/lh?charset=utf8')

sql = ''' select * from test; '''
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
# 输出employee表的查询结果
print(df.head())

注意mysql的链接地址的写法 pymysql

存入

import pandas as pd
import tushare as ts
from sqlalchemy import create_engine


engine = create_engine('mysql+pymysql://root:root@localhost:3306/lh?charset=utf8')

sql = ''' select * from test; '''
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
# 输出employee表的查询结果
print(df.head())

df = pd.DataFrame({'id': [1, 2, 3, 4], 'name': ['zhangsan', 'lisi', 'wangwu', 'zhuliu']})
print(df)
# index 为True会把dateFrame的index存进去
df.to_sql( 'dd',engine,index=False );


# 存股票
ts.set_token('048c7580595543d905c115ffa9d95a36e2b7e926cd7ebc078edd0160')
pro = ts.pro_api()

data = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')

# index 为True会把dateFrame的index存进去
data.to_sql( 'ddddd',engine,index=False ); 

# pd.io.sql.to_sql(data,'tablename',con=conn, schema='lh', if_exists='fail')